import db from './db'

export type Record = {
	user_id: string
	group_id: string
	bot_id: string
	app: string
	plugin: string
	platform: string
	time: number
}

export function addUser(user_id, user_name) {
	return db.run(`INSERT OR REPLACE INTO users (user_id, user_name) VALUES (?, ?)`, [user_id, user_name])
}

export function findPlugin(name) {
	return db.get(`SELECT * FROM plugins WHERE plugin = ?`, [name])
}

export function addGroup(group_id, name) {
	return db.run(`INSERT OR REPLACE INTO groups (group_id, group_name) VALUES (?, ?)`,
		[group_id, name])
}

export function addBot(bot_id, name, type, platform) {
	return db.run(`INSERT OR REPLACE INTO bots (bot_id, bot_name, bot_type, platform) VALUES (?, ?, ?, ?)`,
		[bot_id, name, type, platform])
}

export function record(user_id, group_id, bot_id, app, plugin, time) {
	return db.run(`INSERT INTO records (user_id, group_id, bot_id, app, plugin, time) VALUES (?, ?, ?, ?, ?, ?)`,
		[user_id, group_id, bot_id, app, plugin, time])
}
/** 按类型统计机器人数量，降序排列
 * 格式：
   bot_type_A: 10
   bot_type_B: 8
 */
export function botByType(callback: (bot_type: string, count: number) => void) {
	return db.each(`SELECT bot_type, COUNT(*) as count FROM bots GROUP BY bot_type`,
		(_, { bot_type, count }) => callback(bot_type, count))
}

export function botByPlatform(bot_type: string,
	plugins: string[],
	platform: string[],
	start: number,
	end: number,
	callback: (platform: string, count: number) => void) {
	return db.each(`SELECT platform, COUNT(DISTINCT records.bot_id) as count FROM records
		INNER JOIN bots ON records.bot_id = bots.bot_id
		WHERE bots.bot_type = ?
		${platform.length ? `AND platform IN (?${',?'.repeat(platform.length - 1)})` : ''}
		AND time > ? AND time < ?
		${plugins.length ? `AND plugin IN (?${',?'.repeat(plugins.length - 1)})` : ''}
		GROUP BY platform`,
		[bot_type, ...platform, start, end, ...plugins],
		(_, { platform, count }) => callback(platform, count))
}

export function botByApp(bot_type: string,
	plugins: string[],
	platform: string[],
	start: number,
	end: number,
	callback: (app: string, plugin: string, count: number) => void) {
	return db.each(`SELECT app, plugin, COUNT(*) as count FROM records
		WHERE bot_id IN (SELECT bot_id FROM bots WHERE bot_type = ?
		${platform.length ? `AND platform IN (?${',?'.repeat(platform.length - 1)})` : ''}
		) AND time > ? AND time < ?
		${plugins.length ? `AND plugin IN (?${',?'.repeat(plugins.length - 1)})` : ''}
		GROUP BY app`,
		[bot_type, ...platform, start, end, ...plugins],
		(_, { app, plugin, count }) => callback(app, plugin, count))
}

/** 排行，降序排列
 * 格式：
   type_A:
	 name_A: 200
	 name_B: 100
   type_B:
	 name_C: 300
 */
export function rank(type, start, end) {
	return db.all(`SELECT ${type}, COUNT(*) FROM records
		WHERE time > ? AND time < ? GROUP BY ${type} ORDER BY COUNT(*) DESC`, [start, end])
}
